PostgreSQL 自定义示例库
1 背景知识
本文主要介绍在测试场景中如何快速生成一些测试数据用于文档编写，性能测试，业务测试等。
2 快速生成数据
INSERT INTO t01 VALUES(generate_series(1,99999999));
CREATE TABLE t01 (id int, name varchar(50));
INSERT INTO t01 VALUEStext);
3 顺序数
例子，生成一批顺序值。
SELECT id FROM generate_series(1,10) t(id);
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
4 随机数
random()
例子，生成一批随机整型
SELECT int FROM generate_series(1,10;
int4
------
14
82
25
75
4
75
26
87
84
22
(10 rows)
5 随机字符串
可以使用 md5text 生成一批随机字符串
select md5text) from generate_series(1,10;
md5
----------------------------------
ba1f4f4b0073f61145a821c14437230d
a76b09292c1449ebdccad39bcb5864c0
d58f5ebe43f631e7b5b82e070a05e929
0c0d3971205dc6bd355e9a60b29a4c6d
bd437e87fd904ed6ecc80ed782abac7d
71aea571d8c0cd536de53fd2be8dd461
e32e105db58f9d39245e3e2b27680812
174f491a2ec7a3498cab45d3ce8a4277
563a7c389722f746378987b9c4d9bede
6e8231c4b7d9a5cfaae2a3e0cef22f24
(10 rows)
6 重复字符串
可以使用 repeat(‘abc’, 10) 生成重复2次的随机字符串。
SELECT repeattext),2) FROM generate_series(1,10;
repeat
------------------------------------------------------------------
616d0a07a2b61cd923a14cb3bef06252616d0a07a2b61cd923a14cb3bef06252
73bc0d516a46182b484530f5e153085e73bc0d516a46182b484530f5e153085e
e745a65dbe0b4ef0d2a063487bbbe3d6e745a65dbe0b4ef0d2a063487bbbe3d6
90f9b8b18b3eb095f412e3651f0a946c90f9b8b18b3eb095f412e3651f0a946c
b300f78b20ac9a9534a46e9dfd488761b300f78b20ac9a9534a46e9dfd488761
a3d55c275f1e0f828c4e6863d4751d06a3d55c275f1e0f828c4e6863d4751d06
40e609dbe208fc66372b1c829018097140e609dbe208fc66372b1c8290180971
f661298e28403bc3005ac3aebae49e16f661298e28403bc3005ac3aebae49e16
10d0641e40164a238224d2e16a28764710d0641e40164a238224d2e16a287647
450e599890935df576e20c457691c421450e599890935df576e20c457691c421
(10 rows)
7 随机中文
create or replace function gen_hanzi(int) returns text as $
declare
res text;
begin
if $1 >=1 then
select string_aggint), '') into res from generate_series(1,$1;
return res;
end if;
return null;
end;
$ language plpgsql strict;
postgres=# select gen_hanzi(10) from generate_series(1,10);
gen_hanzi
----------------------
騾歵癮崪圚祯骤氾準赔
縬寱癱办戾薶窍爉充環
鷊赶輪肸蒹焷尮禀漽湯
庰槖诤蜞礀链惧珿憗腽
憭釃轮訞陡切瀰煈瘐獵
韸琵慆蝾啈響夐捶燚積
菥芉阣瀤樂潾敾糩镽礕
廂垅欳事鎤懯劑搯蔷窡
覤綊伱鳪散噹镄灳毯杸
鳀倯鰂錾牓晟挗觑镈壯
(10 rows)
8 随机数组
CREATE OR REPLACE FUNCTION gen_rand_arr(int,int) returns int[] as $
select array_aggint) from generate_series(1,$2;
$ language sql strict;
SELECT gen_rand_arr(100,10) FROM generate_series(1,10);
gen_rand_arr
---------------------------------
{69,11,12,70,7,41,81,95,83,17}
{26,79,20,21,64,64,51,90,38,38}
{3,64,46,28,26,55,39,12,69,76}
{66,38,87,78,8,94,18,88,89,1}
{6,14,81,26,36,45,90,87,35,28}
{25,38,91,71,67,17,26,5,29,95}
{82,94,32,69,72,40,63,90,29,51}
{91,34,66,72,60,1,17,50,88,51}
{77,13,89,69,84,56,86,10,61,14}
{5,43,8,38,11,80,78,74,70,6}
(10 rows)
9 连接符
postgres=# select concat('a', ' ', 'b');
//屏幕输出：
concat
--------
a b
(1 row)
10 自定义函数
通过自定义函数，可以生成很多有趣的数据。
11 随机身份证号
CREATE OR REPLACE FUNCTION gen_id(
a date,
b date
)
returns text as $
select lpadtext, 2, '0' ||
lpadtext, 2, '0' ||
lpadtext, 2, '0' ||
to_char(a + int, 'yyyymmdd' ||
lpadtext, 2, '0' ||
random()::int ||
(case when random()*10 >9 then 'X' else text end ;
$ language sql strict;
SELECT gen_id('1900-01-01', '2017-10-16') FROM generate_series(1,10);
gen_id
--------------------
25614020061108330X
49507919010403271X
96764619970119860X
915005193407306113
551360192005045415
430005192611170108
299138191310237806
95149919670723980X
542053198501097403
482334198309182411
(10 rows)
12 自我循环插入
DO LANGUAGE 'plpgsql' -- 指定匿名块的语言，可省略默认为plpgsql
$BODY$
BEGIN
for i in 1..8 loop
INSERT INTO t01 SELECT * FROM t01;
END loop;
END
$BODY$;